--------------------------------------------------------
--  File created - Wednesday-December-11-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table STAFF
--------------------------------------------------------

  CREATE TABLE "VINAMILK_RELEASE"."STAFF" 
   (	"STAFF_ID" NUMBER(20,1), 
	"STAFF_CODE" VARCHAR2(50 BYTE), 
	"NAME" NVARCHAR2(100), 
	"ADDRESS" NVARCHAR2(50), 
	"STREET" NVARCHAR2(100), 
	"COUNTRY" NVARCHAR2(50), 
	"PHONE" VARCHAR2(20 BYTE), 
	"MOBILE" VARCHAR2(20 BYTE), 
	"MAIL" VARCHAR2(50 BYTE), 
	"SEX" NUMBER(1,0) DEFAULT 1, 
	"START_DATE" DATE, 
	"EDUCATION_ID" VARCHAR2(50 BYTE), 
	"POSITION_ID" VARCHAR2(50 BYTE), 
	"CAT_ID" NUMBER, 
	"STAFF_OWNER_ID" NUMBER, 
	"BIRTHDAY" DATE, 
	"SHOP_ID" NUMBER(20,0), 
	"AREA_CODE" VARCHAR2(40 BYTE), 
	"CREATE_USER" VARCHAR2(50 BYTE), 
	"UPDATE_USER" VARCHAR2(50 BYTE), 
	"CREATE_TIME" DATE, 
	"UPDATE_TIME" DATE, 
	"CHANNEL_TYPE_ID" NUMBER(10,0), 
	"STATUS" NUMBER(10,0), 
	"PASSWORD" VARCHAR2(100 BYTE), 
	"NUMBER_LOGIN_FAIL" NUMBER, 
	"LOCKSTATUS" NUMBER(10,0) DEFAULT 0, 
	"PLAN" NUMBER(20,0), 
	"UPDATE_PLAN" DATE, 
	"LAST_APPROVE_ORDER" DATE, 
	"LAST_ORDER" DATE, 
	"SKU" NUMBER(20,0), 
	"ROLE_TYPE" NUMBER(38,0), 
	"RESET_THRESHOLD" NUMBER(2,0) DEFAULT 5, 
	"DEVICE_IMEI" VARCHAR2(50 BYTE), 
	"STAFF_TYPE" VARCHAR2(40 BYTE), 
	"SKU_AMOUNT" NUMBER(20,0), 
	"IS_DELETE_DATA" NUMBER(2,0) DEFAULT 0, 
	"NAME_TEXT" VARCHAR2(100 BYTE), 
	"IS_GET_DEVICE_INFO" NUMBER(1,0), 
	"CHECK_TYPE" NUMBER(1,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
 

   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STAFF_ID" IS 'id bang NV';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STAFF_CODE" IS 'Ma nhan vien';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."NAME" IS 'Ten nhan vien';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."ADDRESS" IS 'Dia chi';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STREET" IS 'Duong';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."COUNTRY" IS 'Dat nuoc';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."PHONE" IS 'so co dinh';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."MOBILE" IS 'di dong';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."MAIL" IS 'email';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."SEX" IS 'gioi tinh: 1:Nam, 0: nu';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."START_DATE" IS 'Ngay vao lam';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."EDUCATION_ID" IS 'Trinh do';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."POSITION_ID" IS 'vi tri';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."CAT_ID" IS 'chua dung';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STAFF_OWNER_ID" IS 'ID nhan vien quan ly';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."BIRTHDAY" IS 'ngay sinh';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."SHOP_ID" IS 'id NPP';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."AREA_CODE" IS 'Ma dia ban';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."CREATE_USER" IS 'nguoi tao';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."UPDATE_USER" IS 'nguoi cap nhat';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."CREATE_TIME" IS 'ngay tao';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."UPDATE_TIME" IS 'ngay cap nhat';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."CHANNEL_TYPE_ID" IS 'Loai kenh join sang bang channel_type';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STATUS" IS 'Trang thai: 1:hoat dong,0: ngung hoat dong';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."PASSWORD" IS 'mat khau';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."NUMBER_LOGIN_FAIL" IS 'so lan dang nhap sai';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."LOCKSTATUS" IS '0: binh thuong, 1: lock';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."PLAN" IS 'ke hoach ngay';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."UPDATE_PLAN" IS 'ngay cap nhat ke hoach ngay';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."LAST_APPROVE_ORDER" IS 'don hang cuoi cung duoc duyet';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."LAST_ORDER" IS 'don hang cuoi cung da dat';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."SKU" IS 'sku ke hoach';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."ROLE_TYPE" IS '1: NVBH, 2: GSNPP; 3: TBHV; 4 KTNPP; 5: GD Mien, 6: VNM, 7: TNKD theo mien , 10: GSKA, 11: TVKA, 12: GSM KA';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."STAFF"."STAFF_TYPE" IS 'loai nvbh';
--------------------------------------------------------
--  DDL for Index SALEDSREP_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "VINAMILK_RELEASE"."SALEDSREP_PK" ON "VINAMILK_RELEASE"."STAFF" ("STAFF_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
--------------------------------------------------------
--  Constraints for Table STAFF
--------------------------------------------------------

  ALTER TABLE "VINAMILK_RELEASE"."STAFF" ADD CONSTRAINT "SALEDSREP_PK" PRIMARY KEY ("STAFF_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA"  ENABLE;
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("STAFF_ID" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("STAFF_CODE" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("NAME" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("SEX" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("CREATE_USER" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."STAFF" MODIFY ("CREATE_TIME" NOT NULL ENABLE);

--------------------------------------------------------
--  DDL for Trigger STAFF_LOG_TRIGGER
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "VINAMILK_RELEASE"."STAFF_LOG_TRIGGER" 
AFTER INSERT OR DELETE OR UPDATE ON STAFF 
FOR EACH ROW
BEGIN
    IF INSERTING THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'STAFF',:new.STAFF_ID,1,:new.STAFF_ID,:new.shop_id,sysdate);
    ELSIF (UPDATING AND ( ((:new.STAFF_ID IS NULL AND :old.STAFF_ID IS NOT NULL) OR (:new.STAFF_ID IS NOT NULL AND :old.STAFF_ID IS NULL) OR :new.STAFF_ID <> :old.STAFF_ID) OR ((:new.STAFF_CODE IS NULL AND :old.STAFF_CODE IS NOT NULL) OR (:new.STAFF_CODE IS NOT NULL AND :old.STAFF_CODE IS NULL) OR :new.STAFF_CODE <> :old.STAFF_CODE) OR ((:new.NAME IS NULL AND :old.NAME IS NOT NULL) OR (:new.NAME IS NOT NULL AND :old.NAME IS NULL) OR :new.NAME <> :old.NAME) OR ((:new.ADDRESS IS NULL AND :old.ADDRESS IS NOT NULL) OR (:new.ADDRESS IS NOT NULL AND :old.ADDRESS IS NULL) OR :new.ADDRESS <> :old.ADDRESS) OR ((:new.STREET IS NULL AND :old.STREET IS NOT NULL) OR (:new.STREET IS NOT NULL AND :old.STREET IS NULL) OR :new.STREET <> :old.STREET) OR ((:new.COUNTRY IS NULL AND :old.COUNTRY IS NOT NULL) OR (:new.COUNTRY IS NOT NULL AND :old.COUNTRY IS NULL) OR :new.COUNTRY <> :old.COUNTRY) OR ((:new.PHONE IS NULL AND :old.PHONE IS NOT NULL) OR (:new.PHONE IS NOT NULL AND :old.PHONE IS NULL) OR :new.PHONE <> :old.PHONE) OR ((:new.MOBILE IS NULL AND :old.MOBILE IS NOT NULL) OR (:new.MOBILE IS NOT NULL AND :old.MOBILE IS NULL) OR :new.MOBILE <> :old.MOBILE) OR ((:new.MAIL IS NULL AND :old.MAIL IS NOT NULL) OR (:new.MAIL IS NOT NULL AND :old.MAIL IS NULL) OR :new.MAIL <> :old.MAIL) OR ((:new.SEX IS NULL AND :old.SEX IS NOT NULL) OR (:new.SEX IS NOT NULL AND :old.SEX IS NULL) OR :new.SEX <> :old.SEX) OR ((:new.START_DATE IS NULL AND :old.START_DATE IS NOT NULL) OR (:new.START_DATE IS NOT NULL AND :old.START_DATE IS NULL) OR :new.START_DATE <> :old.START_DATE) OR ((:new.EDUCATION_ID IS NULL AND :old.EDUCATION_ID IS NOT NULL) OR (:new.EDUCATION_ID IS NOT NULL AND :old.EDUCATION_ID IS NULL) OR :new.EDUCATION_ID <> :old.EDUCATION_ID) OR ((:new.POSITION_ID IS NULL AND :old.POSITION_ID IS NOT NULL) OR (:new.POSITION_ID IS NOT NULL AND :old.POSITION_ID IS NULL) OR :new.POSITION_ID <> :old.POSITION_ID) OR ((:new.CAT_ID IS NULL AND :old.CAT_ID IS NOT NULL) OR (:new.CAT_ID IS NOT NULL AND :old.CAT_ID IS NULL) OR :new.CAT_ID <> :old.CAT_ID) OR ((:new.STAFF_OWNER_ID IS NULL AND :old.STAFF_OWNER_ID IS NOT NULL) OR (:new.STAFF_OWNER_ID IS NOT NULL AND :old.STAFF_OWNER_ID IS NULL) OR :new.STAFF_OWNER_ID <> :old.STAFF_OWNER_ID) OR ((:new.BIRTHDAY IS NULL AND :old.BIRTHDAY IS NOT NULL) OR (:new.BIRTHDAY IS NOT NULL AND :old.BIRTHDAY IS NULL) OR :new.BIRTHDAY <> :old.BIRTHDAY) OR ((:new.SHOP_ID IS NULL AND :old.SHOP_ID IS NOT NULL) OR (:new.SHOP_ID IS NOT NULL AND :old.SHOP_ID IS NULL) OR :new.SHOP_ID <> :old.SHOP_ID) OR ((:new.AREA_CODE IS NULL AND :old.AREA_CODE IS NOT NULL) OR (:new.AREA_CODE IS NOT NULL AND :old.AREA_CODE IS NULL) OR :new.AREA_CODE <> :old.AREA_CODE) OR ((:new.CHANNEL_TYPE_ID IS NULL AND :old.CHANNEL_TYPE_ID IS NOT NULL) OR (:new.CHANNEL_TYPE_ID IS NOT NULL AND :old.CHANNEL_TYPE_ID IS NULL) OR :new.CHANNEL_TYPE_ID <> :old.CHANNEL_TYPE_ID) OR ((:new.STATUS IS NULL AND :old.STATUS IS NOT NULL) OR (:new.STATUS IS NOT NULL AND :old.STATUS IS NULL) OR :new.STATUS <> :old.STATUS) OR ((:new.PASSWORD IS NULL AND :old.PASSWORD IS NOT NULL) OR (:new.PASSWORD IS NOT NULL AND :old.PASSWORD IS NULL) OR :new.PASSWORD <> :old.PASSWORD) OR ((:new.NUMBER_LOGIN_FAIL IS NULL AND :old.NUMBER_LOGIN_FAIL IS NOT NULL) OR (:new.NUMBER_LOGIN_FAIL IS NOT NULL AND :old.NUMBER_LOGIN_FAIL IS NULL) OR :new.NUMBER_LOGIN_FAIL <> :old.NUMBER_LOGIN_FAIL) OR ((:new.LOCKSTATUS IS NULL AND :old.LOCKSTATUS IS NOT NULL) OR (:new.LOCKSTATUS IS NOT NULL AND :old.LOCKSTATUS IS NULL) OR :new.LOCKSTATUS <> :old.LOCKSTATUS) OR ((:new.PLAN IS NULL AND :old.PLAN IS NOT NULL) OR (:new.PLAN IS NOT NULL AND :old.PLAN IS NULL) OR :new.PLAN <> :old.PLAN) OR ((:new.UPDATE_PLAN IS NULL AND :old.UPDATE_PLAN IS NOT NULL) OR (:new.UPDATE_PLAN IS NOT NULL AND :old.UPDATE_PLAN IS NULL) OR :new.UPDATE_PLAN <> :old.UPDATE_PLAN) OR ((:new.LAST_APPROVE_ORDER IS NULL AND :old.LAST_APPROVE_ORDER IS NOT NULL) OR (:new.LAST_APPROVE_ORDER IS NOT NULL AND :old.LAST_APPROVE_ORDER IS NULL) OR :new.LAST_APPROVE_ORDER <> :old.LAST_APPROVE_ORDER) OR ((:new.LAST_ORDER IS NULL AND :old.LAST_ORDER IS NOT NULL) OR (:new.LAST_ORDER IS NOT NULL AND :old.LAST_ORDER IS NULL) OR :new.LAST_ORDER <> :old.LAST_ORDER) OR ((:new.SKU IS NULL AND :old.SKU IS NOT NULL) OR (:new.SKU IS NOT NULL AND :old.SKU IS NULL) OR :new.SKU <> :old.SKU) OR ((:new.ROLE_TYPE IS NULL AND :old.ROLE_TYPE IS NOT NULL) OR (:new.ROLE_TYPE IS NOT NULL AND :old.ROLE_TYPE IS NULL) OR :new.ROLE_TYPE <> :old.ROLE_TYPE) OR ((:new.RESET_THRESHOLD IS NULL AND :old.RESET_THRESHOLD IS NOT NULL) OR (:new.RESET_THRESHOLD IS NOT NULL AND :old.RESET_THRESHOLD IS NULL) OR :new.RESET_THRESHOLD <> :old.RESET_THRESHOLD) OR ((:new.DEVICE_IMEI IS NULL AND :old.DEVICE_IMEI IS NOT NULL) OR (:new.DEVICE_IMEI IS NOT NULL AND :old.DEVICE_IMEI IS NULL) OR :new.DEVICE_IMEI <> :old.DEVICE_IMEI) OR ((:new.STAFF_TYPE IS NULL AND :old.STAFF_TYPE IS NOT NULL) OR (:new.STAFF_TYPE IS NOT NULL AND :old.STAFF_TYPE IS NULL) OR :new.STAFF_TYPE <> :old.STAFF_TYPE) OR ((:new.SKU_AMOUNT IS NULL AND :old.SKU_AMOUNT IS NOT NULL) OR (:new.SKU_AMOUNT IS NOT NULL AND :old.SKU_AMOUNT IS NULL) OR :new.SKU_AMOUNT <> :old.SKU_AMOUNT ) OR ((:new.NAME_TEXT IS NULL AND :old.NAME_TEXT IS NOT NULL) OR (:new.NAME_TEXT IS NOT NULL AND :old.NAME_TEXT IS NULL) OR :new.NAME_TEXT <> :old.NAME_TEXT) )) THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'STAFF',:new.STAFF_ID,2,:new.STAFF_ID,:new.shop_id,sysdate);
    ELSIF DELETING THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'STAFF',:old.STAFF_ID,3,:old.STAFF_ID,:old.shop_id,sysdate);
    END IF;  
END;

/
ALTER TRIGGER "VINAMILK_RELEASE"."STAFF_LOG_TRIGGER" ENABLE;


--------------------------------------------------------
--  File created - Wednesday-December-11-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table SHOP
--------------------------------------------------------

  CREATE TABLE "VINAMILK_RELEASE"."SHOP" 
   (	"SHOP_ID" NUMBER(20,1), 
	"SHOP_CODE" NVARCHAR2(100), 
	"SHOP_NAME" NVARCHAR2(100), 
	"PARENT_SHOP_ID" NUMBER(20,0), 
	"STREET" NVARCHAR2(100), 
	"WARD" NVARCHAR2(20), 
	"DISTRICT" NVARCHAR2(50), 
	"PROVINCE" NVARCHAR2(50), 
	"AREA_CODE" NVARCHAR2(20), 
	"COUNTRY" VARCHAR2(20 BYTE), 
	"PHONE" VARCHAR2(100 BYTE), 
	"TELEPHONE" VARCHAR2(100 BYTE), 
	"SHOP_TYPE" NUMBER(2,0) DEFAULT 1, 
	"CREATE_USER" VARCHAR2(50 BYTE), 
	"UPDATE_USER" VARCHAR2(50 BYTE), 
	"CREATE_DATE" DATE, 
	"UPDATE_DATE" DATE, 
	"CHANNEL_TYPE_ID" NUMBER(10,0), 
	"EMAIL" VARCHAR2(50 BYTE), 
	"LAT" FLOAT(126), 
	"LNG" FLOAT(126), 
	"DISTANCE_ORDER" NUMBER(10,0) DEFAULT 200, 
	"DISTANCE_TRAINING" NUMBER(10,0) DEFAULT 200, 
	"SHOP_NAME_TEXT" VARCHAR2(100 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
 

   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."SHOP_ID" IS 'id cua NPP';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."SHOP_CODE" IS 'ma NPP';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."SHOP_NAME" IS 'Ten NPP';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."PARENT_SHOP_ID" IS 'ID shop cha';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."STREET" IS 'Duong';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."WARD" IS 'Xa';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."DISTRICT" IS 'Huyen';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."PROVINCE" IS 'Tinh';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."AREA_CODE" IS 'ma dia ban';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."COUNTRY" IS 'ma dat nuoc';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."PHONE" IS 'di dong';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."TELEPHONE" IS 'co dinh';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."SHOP_TYPE" IS '1: NPP(default) 2: Vinamilk';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."CREATE_USER" IS 'nguoi tao';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."UPDATE_USER" IS 'nguoi cap nhat';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."CREATE_DATE" IS 'ngay tao';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."UPDATE_DATE" IS 'ngay cap nhat';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."CHANNEL_TYPE_ID" IS 'Loai kenh join sang bang channel_type';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."SHOP"."EMAIL" IS 'email giam sat nhan phan phoi';
--------------------------------------------------------
--  DDL for Index DISTRIBUTOR_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "VINAMILK_RELEASE"."DISTRIBUTOR_PK" ON "VINAMILK_RELEASE"."SHOP" ("SHOP_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
--------------------------------------------------------
--  Constraints for Table SHOP
--------------------------------------------------------

  ALTER TABLE "VINAMILK_RELEASE"."SHOP" ADD CONSTRAINT "DISTRIBUTOR_PK" PRIMARY KEY ("SHOP_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA"  ENABLE;
 
  ALTER TABLE "VINAMILK_RELEASE"."SHOP" MODIFY ("SHOP_ID" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."SHOP" MODIFY ("SHOP_CODE" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."SHOP" MODIFY ("SHOP_NAME" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."SHOP" MODIFY ("CREATE_USER" NOT NULL ENABLE);
 
  ALTER TABLE "VINAMILK_RELEASE"."SHOP" MODIFY ("CREATE_DATE" NOT NULL ENABLE);

--------------------------------------------------------
--  DDL for Trigger SHOP_LOG_TRIG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "VINAMILK_RELEASE"."SHOP_LOG_TRIG" 
AFTER INSERT OR DELETE OR UPDATE ON SHOP 
FOR EACH ROW
BEGIN
    IF INSERTING THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'SHOP',:new.SHOP_ID,1,null,:new.SHOP_ID,sysdate);
    ELSIF (UPDATING AND ( ((:new.SHOP_ID IS NULL AND :old.SHOP_ID IS NOT NULL) OR (:new.SHOP_ID IS NOT NULL AND :old.SHOP_ID IS NULL) OR :new.SHOP_ID <> :old.SHOP_ID) OR ((:new.SHOP_CODE IS NULL AND :old.SHOP_CODE IS NOT NULL) OR (:new.SHOP_CODE IS NOT NULL AND :old.SHOP_CODE IS NULL) OR :new.SHOP_CODE <> :old.SHOP_CODE) OR ((:new.SHOP_NAME IS NULL AND :old.SHOP_NAME IS NOT NULL) OR (:new.SHOP_NAME IS NOT NULL AND :old.SHOP_NAME IS NULL) OR :new.SHOP_NAME <> :old.SHOP_NAME) OR ((:new.PARENT_SHOP_ID IS NULL AND :old.PARENT_SHOP_ID IS NOT NULL) OR (:new.PARENT_SHOP_ID IS NOT NULL AND :old.PARENT_SHOP_ID IS NULL) OR :new.PARENT_SHOP_ID <> :old.PARENT_SHOP_ID) OR ((:new.STREET IS NULL AND :old.STREET IS NOT NULL) OR (:new.STREET IS NOT NULL AND :old.STREET IS NULL) OR :new.STREET <> :old.STREET) OR ((:new.WARD IS NULL AND :old.WARD IS NOT NULL) OR (:new.WARD IS NOT NULL AND :old.WARD IS NULL) OR :new.WARD <> :old.WARD) OR ((:new.DISTRICT IS NULL AND :old.DISTRICT IS NOT NULL) OR (:new.DISTRICT IS NOT NULL AND :old.DISTRICT IS NULL) OR :new.DISTRICT <> :old.DISTRICT) OR ((:new.PROVINCE IS NULL AND :old.PROVINCE IS NOT NULL) OR (:new.PROVINCE IS NOT NULL AND :old.PROVINCE IS NULL) OR :new.PROVINCE <> :old.PROVINCE) OR ((:new.AREA_CODE IS NULL AND :old.AREA_CODE IS NOT NULL) OR (:new.AREA_CODE IS NOT NULL AND :old.AREA_CODE IS NULL) OR :new.AREA_CODE <> :old.AREA_CODE) OR ((:new.COUNTRY IS NULL AND :old.COUNTRY IS NOT NULL) OR (:new.COUNTRY IS NOT NULL AND :old.COUNTRY IS NULL) OR :new.COUNTRY <> :old.COUNTRY) OR ((:new.PHONE IS NULL AND :old.PHONE IS NOT NULL) OR (:new.PHONE IS NOT NULL AND :old.PHONE IS NULL) OR :new.PHONE <> :old.PHONE) OR ((:new.TELEPHONE IS NULL AND :old.TELEPHONE IS NOT NULL) OR (:new.TELEPHONE IS NOT NULL AND :old.TELEPHONE IS NULL) OR :new.TELEPHONE <> :old.TELEPHONE) OR ((:new.SHOP_TYPE IS NULL AND :old.SHOP_TYPE IS NOT NULL) OR (:new.SHOP_TYPE IS NOT NULL AND :old.SHOP_TYPE IS NULL) OR :new.SHOP_TYPE <> :old.SHOP_TYPE) OR ((:new.CHANNEL_TYPE_ID IS NULL AND :old.CHANNEL_TYPE_ID IS NOT NULL) OR (:new.CHANNEL_TYPE_ID IS NOT NULL AND :old.CHANNEL_TYPE_ID IS NULL) OR :new.CHANNEL_TYPE_ID <> :old.CHANNEL_TYPE_ID) OR ((:new.EMAIL IS NULL AND :old.EMAIL IS NOT NULL) OR (:new.EMAIL IS NOT NULL AND :old.EMAIL IS NULL) OR :new.EMAIL <> :old.EMAIL) OR ((:new.LAT IS NULL AND :old.LAT IS NOT NULL) OR (:new.LAT IS NOT NULL AND :old.LAT IS NULL) OR :new.LAT <> :old.LAT) OR ((:new.LNG IS NULL AND :old.LNG IS NOT NULL) OR (:new.LNG IS NOT NULL AND :old.LNG IS NULL) OR :new.LNG <> :old.LNG) OR ((:new.DISTANCE_ORDER IS NULL AND :old.DISTANCE_ORDER IS NOT NULL) OR (:new.DISTANCE_ORDER IS NOT NULL AND :old.DISTANCE_ORDER IS NULL) OR :new.DISTANCE_ORDER <> :old.DISTANCE_ORDER) OR ((:new.DISTANCE_TRAINING IS NULL AND :old.DISTANCE_TRAINING IS NOT NULL) OR (:new.DISTANCE_TRAINING IS NOT NULL AND :old.DISTANCE_TRAINING IS NULL) OR :new.DISTANCE_TRAINING <> :old.DISTANCE_TRAINING) OR ((:new.SHOP_NAME_TEXT IS NULL AND :old.SHOP_NAME_TEXT IS NOT NULL) OR (:new.SHOP_NAME_TEXT IS NOT NULL AND :old.SHOP_NAME_TEXT IS NULL) OR :new.SHOP_NAME_TEXT <> :old.SHOP_NAME_TEXT) )) THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'SHOP',:new.SHOP_ID,2,null,:new.SHOP_ID,sysdate);
    ELSIF DELETING THEN
      insert into database_log (id,table_name,table_id, action,  staff_id, shop_id, create_date) values
        (database_log_seq.nextval,'SHOP',:old.SHOP_ID,3,null,:old.SHOP_ID,sysdate);
    END IF;
END;

/
ALTER TRIGGER "VINAMILK_RELEASE"."SHOP_LOG_TRIG" ENABLE;


--------------------------------------------------------
--  File created - Wednesday-December-11-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table GCM_REGISTRATION
--------------------------------------------------------

  CREATE TABLE "VINAMILK_RELEASE"."GCM_REGISTRATION" 
   (	"ID" NUMBER(20,0), 
	"SHOP_ID" NUMBER(20,0), 
	"STAFF_ID" NUMBER(20,0), 
	"STAFF_CODE" VARCHAR2(50 BYTE), 
	"REGISTRATION_KEY" VARCHAR2(200 BYTE), 
	"NOTE" VARCHAR2(200 BYTE), 
	"CREATE_DATE" DATE, 
	"UPDATE_DATE" DATE, 
	"CREATE_USER" VARCHAR2(50 BYTE), 
	"UPDATE_USER" VARCHAR2(50 BYTE), 
	"STATUS" NUMBER(1,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
 

   COMMENT ON COLUMN "VINAMILK_RELEASE"."GCM_REGISTRATION"."REGISTRATION_KEY" IS 'registration id do google cap';
 
   COMMENT ON COLUMN "VINAMILK_RELEASE"."GCM_REGISTRATION"."NOTE" IS 'ghi chu (co the dung cho ghi chu loi)';
--------------------------------------------------------
--  DDL for Index GCM_REGISTRATION_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "VINAMILK_RELEASE"."GCM_REGISTRATION_PK" ON "VINAMILK_RELEASE"."GCM_REGISTRATION" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA" ;
--------------------------------------------------------
--  Constraints for Table GCM_REGISTRATION
--------------------------------------------------------

  ALTER TABLE "VINAMILK_RELEASE"."GCM_REGISTRATION" ADD CONSTRAINT "GCM_REGISTRATION_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VNM_DATA"  ENABLE;

